The Google Adwords API might not return a consistent number of fields for search query summary (SQ) data. To correct the problem, modify the following SAS^® Data Integration Studio job and ucmacro. These steps can be used to modify any of the weba_sebd_2xxx jobs.

1. Determine which fields are not being sent by Google Adwords API. Review the fields not being sent and make sure that there are no additional problems by reviewing the Google Adwords API blog and FAQ documentation.
   Conversions and ConversionRate are examples of fields that might not be returned if an e-mail account is set up incorrectly.
2. Modify the file sebd_google_extract_col_chk.sas.
   This macro validates the columns within the extract data sets for each of the Google Adwords:
   For Windows: !sasroot\weba\ucmacros
   For UNIX: !sasroot/ucmacros/weba
   
   • Add sqcmpvars to %global statement
   • Add a check for the existence of the Conversions and ConversionRate fields:

        %sqcmpvars=;
        %if &fields = SQ %then %do;
          proc sql noprint;
            create table cpm_chk as
              select name
                from vars
               where kupcase(name) in ('CONVERSIONS','CONVERSIONRATE');
     
            select "'"||kcompress(name,' ')||"'"
              into :sqcmpvars separated by " "
              from cpm_chk;
     
            select kcompress(put(nobs,best.),' ')
              into :sqvars
              from dictionary.tables
             where libname="WORK" and memname="VARS";
          quit;
        %end;
     

   • Modify the SQ fields code within the check for field existence to account for the fact that the Conversions and ConversionRate fields might not always occur. For example,

     %if &fields = SQ %then %do;
            fields{&sqvars} $32. field1-field&sqvars ('ResponseDate'
                                            'CampaignId'
                                            'AdGroupId'
                                            'Query'
                                            'MatchType'
                                            'CreativeId'
                                            'AdWordsType'
                                            'CreativeDestUrl'
                                            'Impressions'
                                            'Clicks'
                                            'CTR'
                                            'CPC'
                                            'Cost'
                                            'AveragePosition'
                                            &sqcmpvars  );
        %end;
     

   • Save the updated macro using the best practices established for your site.
3. Modify weba_sebd_024_google_search_query_extract_parameterized job:
   • Open the weba_sebd_024_google_search_query_extract_parameterized job
   • Right-click on Extract, node 2, and select Properties
   • Select Mapping tab
   • Modify Conversions field in the Target Table by clicking on Expression Cell
     Select Advanced
     Update the expression as follows:

     %if &sqcmpvars = %str() %then %do;
       .
     %end;
     %else %do;
       INPUT(Conversions,best.32)
     %end;
     

   • Modify ConversionRate field in Target Table by clicking on Expression Cell
     Select Advanced.
     Update the expression as follows:

     %if &sqcmpvars = %str() %then %do;
       .
     %end;
     %else %do;
       INPUT(ConversionRate,best.32)
     %end;
     

4. Save the job changes.
5. Test the changes on extract data sets that do and do not contain the Conversions and ConversionRate fields by running the weba_sebd_024_google_search_query job.
6. Export the weba_sebd_024_google_search_query job code by selecting Schedule and Redeploy.
   
   

   Operating System and Release Information

   Product Family	Product	System	Product Release		SAS Release
   			Reported	Fixed*	Reported	Fixed*
   SAS System	SAS Web Analytics	Solaris for x64	5.31	5.31	9.2 TS2M0	9.2 TS2M0
   		Linux for x64	5.31	5.31	9.2 TS2M0	9.2 TS2M0
   		HP-UX IPF	5.31	5.31	9.2 TS2M0	9.2 TS2M0
   		64-bit Enabled Solaris	5.31	5.31	9.2 TS2M0	9.2 TS2M0
   		64-bit Enabled AIX	5.31	5.31	9.2 TS2M0	9.2 TS2M0
   		Windows Vista	5.31	5.31	9.2 TS2M0	9.2 TS2M0
   		Microsoft Windows Server 2003 Standard Edition	5.31	5.31	9.2 TS2M0	9.2 TS2M0
   		Microsoft Windows Server 2003 Enterprise Edition	5.31	5.31	9.2 TS2M0	9.2 TS2M0
   		Microsoft Windows XP Professional	5.31	5.31	9.2 TS2M0	9.2 TS2M0
   		Microsoft Windows Server 2003 Datacenter Edition	5.31	5.31	9.2 TS2M0	9.2 TS2M0
   		Microsoft® Windows® for x64	5.31	5.31	9.2 TS2M0	9.2 TS2M0

   * For software releases that are not yet generally available, the Fixed Release is the software release in which the problem is planned to be fixed.

---

Google Adwords API might not return a consistent number of fields for Search Query summary (SQ) data.

Type:	Usage Note
Priority:

Date Modified:	2012-01-20 19:21:53
Date Created:	2009-12-15 11:08:23